#   根据master文件，与slave文件的每行相比较，根据nan数据的多少，找出需要补全的行
#   不要求标题行在第一行，要求标题行在同一行，要求标题行第一列为‘序号’
#   将多个slave文件向master文件整合 , 对于某一slave文件读取失败不会影响其他文件合并
#   支持多sheet合并, 仅限有序号标题的合并
#   不要求两个文件行数相等, 不要求顺序相同
#   结合PathClass，自动合并文件夹寻找excel文件，将文件名最短的作为master，其余作为slave
#   这是一个比较稳定的版本，可以处理一些不太复杂的表格

from ExcelClass import Sheet
from PathClass import PathClass
import pandas


def get_all_files(arg_path, suffix_names=[]):
    xpath = PathClass(arg_path)
    # print(suffix_names)
    files = xpath.get_files(suffix_names)
    # print(f'files in {xpath.path}:{files}')
    if files:
        master_file = files[0]
        for file in files:
            if len(file) < len(master_file):
                master_file = file
        print(master_file)
        files.remove(master_file)
        return master_file, files
    else:
        return None, None


def combine_all_in_one(path, master_filename, suffix_names):
    for filename in suffix_names:
        sheets = pandas.ExcelFile(path + filename).sheet_names
        for sheet in sheets:
            print(f'slave file name: {filename}, sheet name: {sheet}')
            combine_two_in_one_by_order(path, master_filename, filename, sheet)


def combine_two_in_one_by_order(path, master_filename, slave_filename, sheet='Sheet2'):
    try:
        slave_excel = Sheet(path, slave_filename, sheet)
        master_excel = Sheet(path, master_filename, sheet)
        print(f'{slave_filename}读取成功')
    except:
        print(f'{slave_filename}读取失败')
        # 从文件读取失败, 直接return, 读取下一个从文件
        return

    key_line_num = 1  # 标题行所在行数
    # get master file's row num
    master_row_num = master_excel.get_max_row()
    slave_row_num = slave_excel.get_max_row()
    # print('master_row_num:', master_excel.get_max_row())
    # print('slave_row_num:', slave_excel.get_max_row())
    # traverse each row and compare nan values
    # 目的：从文件行数可能比主文件少，而且顺序不一致
    # 实现：先遍历从文件，再到主文件里寻找对应行
    try:
        for slave_row in range(slave_row_num - key_line_num):
            slave_line_content = slave_excel.get_line(slave_row)
            # print(f'slave row: {slave_row}, slave num: {slave_line_content[0]}')
            for master_row in range(master_row_num - key_line_num):
                master_line_content = master_excel.get_line(slave_row)
                # print(f'i:{i}, {master_excel.get_nan_count_by_line(i)} <{slave_excel.get_nan_count_by_line(i)}' )

                if slave_excel.get_line(slave_row)[0] == master_excel.get_line(master_row)[0] and \
                        master_excel.get_nan_count_by_line(master_row) > slave_excel.get_nan_count_by_line(slave_row):
                    # 如果主文件当前行的空白单元格相比更多
                    # print(f'need to recover on serial: {master_excel.get_serial_nums()}')
                    # print(f'need to recover on serial: {master_line_content[0]},{master_line_content[2]}')
                    # 将符合条件从文件的行覆盖主文件， i+1是为了跳过标题行
                    master_excel.cover_line(master_row + key_line_num, slave_line_content)
        print(f'{slave_filename}合并成功')
    except Exception as e:
        print(f'{slave_filename}合并失败, 原因: {e}')


if __name__ == '__main__':
    file_path = './data_source/others/'
    # master = 'panda_read_excel.xlsx'
    # slave1 = 'panda_read_excel--1.xlsx'
    # slave2 = 'panda_read_excel--2.xlsx'
    # xpath = PathClass(file_path)
    # files = xpath.get_files()
    # combine_two_in_one_by_order(file_path, master, slave1)
    # combine_all_in_one(file_path, master, slave1, slave2)
    # print(get_all_files(file_path))
    master, slaves = get_all_files(file_path)
    combine_all_in_one(file_path, master, slaves)
